package com.coolw.jdbc;

import com.coolw.jdbc.util.DbUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * jdbc测试类
 *
 * @author coolw
 * @date 2023/12/9 11:56
 */
public class JdbcTest {

    private static Connection conn;
    private static Statement stat;
    private static ResultSet rs;

    @Before
    public void before() throws Exception {
        // 获取数据库连接
        conn = DbUtil.getConnection();
        // 获取Statement对象
        stat = conn.createStatement();
    }

    @After
    public void after() {
        // 关闭资源
        DbUtil.close(conn, stat, rs);
    }

    /**
     * 新增
     */
    @Test
    public void testInsert() {
        String sql = "insert into t_user (user_name, password) values ('15000994425', '123456')";
        try {
            // 执行SQL
            int cnt = stat.executeUpdate(sql);
            System.out.println("新增结果:" + (cnt > 0));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询
     */
    @Test
    public void testQuery() {
        String sql = "select * from t_user where user_name = '13817388142'";
        try {
            rs = stat.executeQuery(sql);
            // 使用next()方法逐条遍历结果
            if (rs.next()) {
                String userName = rs.getString("user_name");
                String password = rs.getString("password");
                System.out.println("user_name=" + userName + "\npassword=" + password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改
     */
    @Test
    public void testUpdate() {
        String sql = "update t_user set password = '999999' where user_name = '13817388142'";
        try {
            int cnt = stat.executeUpdate(sql);
            System.out.println("更新结果:" + (cnt > 0));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除
     */
    @Test
    public void testDelete() {
        String sql = "delete from t_user where user_name = '13817388142'";
        try {
            int cnt = stat.executeUpdate(sql);
            System.out.println("删除结果:" + (cnt > 0));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 事务操作
     */
    @Test
    public void testTransaction() {
        String sql1 = "update t_user set password = '456789' where user_name = '13817388142'";
        String sql2 = "update t_user set password = '456789' where user_name = '15000994425'";

        try {
            // 关闭自动提交(开启事务)
            conn.setAutoCommit(false);
            System.out.println("开启事务=========ok");

            // sql1执行
            int sql1Cnt = stat.executeUpdate(sql1);
            System.out.println("sql1执行结果:" + (sql1Cnt > 0));

            // 代码报错，进行事务回滚
            int result = 1 / 0;

            // sql1执行
            int sql2Cnt = stat.executeUpdate(sql2);
            System.out.println("sql2执行结果:" + (sql2Cnt > 0));

            // 提交事务
            conn.commit();
            System.out.println("提交事务=========ok");
        } catch (Exception e) {
            e.printStackTrace();

            // 事务回滚
            try {
                conn.rollback();
                System.out.println("事务回滚=========ok");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}
